package com.trkj.dao.jpa;


import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.trkj.pojos.LmbCousumablePurchasePlan;

public interface LmbCouPurPlanDAO extends CrudRepository<LmbCousumablePurchasePlan, String> {
	@Query("select max(c.cpPlanId) from LmbCousumablePurchasePlan c where c.cpPlanId like ?1%")
    public String findMaxID(String a);
	
	//分页显示采购计划
	@Query("select s from LmbCousumablePurchasePlan s order by s.cpPlanId desc")
	public List<LmbCousumablePurchasePlan> findCurPurPlanByPage(Pageable pageable);
	
	//@Query("select new LmbCousumablePurchasePlan(c.cpPlanId,c.cpPlanTitle,c.cpPlanMakepeople,c.cpPlanApprovepeople,c.cpPlanStatus) from LmbCousumablePurchasePlan c")
	//public List<LmbCousumablePurchasePlan> findAllCurPurPlan();

	//根据审批状态查找采购计划
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanStatus =?1 order by cp.cpPlanId desc")
	public List<LmbCousumablePurchasePlan> findCurPurPlanByStatus(Pageable pageable,String status);
	
	//根据审批状态查找出计划条数
	@Query("select count(*) from LmbCousumablePurchasePlan s where s.cpPlanStatus=?1")
	public long findByCount(String status);
	
	//根据采购标题查询采购计划
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanTitle like ?1% order by cp.cpPlanId desc")
	public List<LmbCousumablePurchasePlan> findCurPurPlanByTitle(Pageable pageable,String title);

	//根据采购标题查找出计划条数
	@Query("select count(*) from LmbCousumablePurchasePlan cp where cp.cpPlanTitle like ?1%")
	public long findByTitleCount(String title);
	
	//根据采购状态查询采购计划
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanPurstatus like ?1% order by cp.cpPlanId desc")
	public List<LmbCousumablePurchasePlan> findBycpPlanPurstatus(Pageable pageable,String purstatus);

	//根据采购状态查询采购计划
	@Query("select count(*) from LmbCousumablePurchasePlan cp where cp.cpPlanPurstatus like ?1%")
	public long findBycpPlanPurstatusCount(String purstatus);
	
	//根据采购状态查询采购计划
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanApprovepeople =?1 and cp.cpPlanStatus='待审批' order by cp.cpPlanApprovedate desc")
	public List<LmbCousumablePurchasePlan> findByApprovepeople(Pageable pageable,BigDecimal spr);

	//根据采购状态查询采购计划
	@Query("select count(*) from LmbCousumablePurchasePlan cp where cp.cpPlanApprovepeople =?1")
	public long CountFindByApprovepeople(BigDecimal spr);
	
	//根据采购状态查询采购计划
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanStatus='已审批'  and cp.cpPlanPurstatus='未采购'")
	public List<LmbCousumablePurchasePlan> findBytj(Pageable pageable);

	//根据采购状态查询采购计划
	@Query("select count(*) from LmbCousumablePurchasePlan cp where cp.cpPlanStatus='已审批'  and cp.cpPlanPurstatus='未采购'")
	public long CountFindBytj();
	
	@Query("select cp from LmbCousumablePurchasePlan cp where cp.cpPlanApprovepeople = ?1  and cp.cpPlanStatus='已审批'")
	public List<LmbCousumablePurchasePlan> findPlanByspr(Pageable pageable,BigDecimal spr);
	
	@Query("select count(cp) from LmbCousumablePurchasePlan cp where cp.cpPlanApprovepeople = ?1  and cp.cpPlanStatus='已审批'")
	public long countfindPlanByspr(BigDecimal spr);
}
